<?php
if (!defined('ABSPATH')) {
    exit();
}
class CMC_Coins_Meta extends PW_DB
{

    /**
     * Get things started
     *
     * @access  public
     * @since   1.0
     */
    public function __construct()
    {

        global $wpdb;

        $this->table_name = $wpdb->base_prefix . CMC_META_DB;
        $this->primary_key = 'id';
        $this->version = '1.0';

    }

    /**
     * Get columns and formats
     *
     * @access  public
     * @since   1.0
     */
    public function get_columns()
    {

        return array(
            'id' => '%d',
            'coin_id' => '%s',
            'description' => '%s',
            'extra_data' => '%s',
        );
    }

    public function cmc_extra_meta_insert($coins_data)
    {

        if (is_array($coins_data)) {
            global $wpdb;
            $query_indexes = "INSERT INTO `" . $this->table_name . "`(`coin_id`, `extra_data`) VALUES ";
            $query_values = [];

            foreach ($coins_data as $id => $coin) {
                if ($coin['extra_info'] != null) {
                    $extra_data = serialize($coin['extra_info']);
                } else {
                    $extra_data = 'N/A';
                }
                $query_values[] = $wpdb->prepare("(%s, %s)", $id, $extra_data);
            }

            $query = $query_indexes . implode(',', $query_values) . "ON DUPLICATE KEY UPDATE extra_data=VALUES(extra_data)";
            return $result = $wpdb->query($query);

        }
    }

    /**
     * Insert coin descriptions into the database
     *
     * @param array $coins_data The coin descriptions to be inserted
     * @return int|false The number of rows affected/inserted, or false on error
     */
    public function cmc_desc_insert($coins_data)
    {
        if (is_array($coins_data)) {
            global $wpdb;
            
            // Build the base query
            $query_indexes = "INSERT INTO `" . $this->table_name . "`(`coin_id`, `description`) VALUES ";
            $placeholders = array();
            $values = array();
            
            foreach ($coins_data as $id => $coin) {
                if ($coin['description'] != null) {
                    $description = preg_replace('/[^ -\x{2122}]\s+|\s*[^ -\x{2122}]/u', '', $coin['description']);
                } else {
                    $description = 'N/A';
                }
                
                // Add placeholders for each row
                $placeholders[] = '(%s, %s)';
                $values[] = $id;
                $values[] = $description;
            }
            
            // Combine the query with placeholders
            $query = $query_indexes . implode(',', $placeholders) . " ON DUPLICATE KEY UPDATE description=VALUES(description)";
            
            // Prepare and execute the query safely
            $prepared_query = $wpdb->prepare($query, $values);
            return $wpdb->query($prepared_query);
        }
        return false;
    }

    /**
     * Get default column values
     *
     * @access  public
     * @since   1.0
     */
    public function get_column_defaults()
    {
        return array(
            'coin_id' => '',
            'extra_data' => '',
            'description' => '',
            'last_updated' => date('Y-m-d H:i:s'),
        );
    }

    /**
     * Check if a coin exists by its ID
     *
     * @param string $coin_ID The ID of the coin to check
     * @return bool Whether the coin exists or not
     */
    public function coin_exists_by_id($coin_ID)
    {
        global $wpdb;

        // Sanitize and escape the coin ID for a database query and use $wpdb->prepare() for insertion
        $sanitized_coin_ID = esc_sql($coin_ID);

        // Perform the database query to check the existence of the coin
        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $this->table_name WHERE coin_id = %s", $sanitized_coin_ID));

        // Return true if the coin exists, otherwise return false
        return ($count == 1) ? true : false;
    }

    /**
     * Get coins description with sanitized and escaped input
     *
     * @param array $args   Optional. Array of arguments for querying coins description.
     * @param bool  $count  Optional. Whether to count the results. Default false.
     * @return mixed        Array of coins description or count of results.
     */
    public function get_coins_desc($args = array(), $count = false)
    {
        global $wpdb;

        // Set default arguments
        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'coin_id' => '',
            'status' => '',
            'orderby' => 'id',
            'order' => 'ASC',
        );
        $args = wp_parse_args($args, $defaults);

        // Sanitize and escape the number input
        $args['number'] = absint($args['number']);

        // Set up the WHERE clause
        $where = '';

        // Sanitize and escape the coin ID input
        if (!empty($args['coin_id'])) {
            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }
            if (is_array($args['coin_id'])) {
                $coin_ids = array_map('esc_sql', $args['coin_id']);
                $where .= " `coin_id` IN('" . implode("','", $coin_ids) . "') ";
            } else {
                $coin_id = esc_sql($args['coin_id']);
                $where .= " `coin_id` = '" . $coin_id . "' ";
            }
        }

        // Sanitize and escape the orderby input
        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];
        if ('total' === $args['orderby'] || 'subtotal' === $args['orderby']) {
            $args['orderby'] = esc_sql($args['orderby']) . '+0';
        }

        // Generate cache key
        $cache_key = (true === $count) ? md5('cmc_coins_desc_count' . serialize($args)) : md5('cmc_coins_desc_' . serialize($args));

        // Get results from cache or perform database query
        $results = wp_cache_get($cache_key, 'coins');
        if (false === $results) {
            if (true === $count) {
                // Perform sanitized database query to count results
                $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT({$this->primary_key}) FROM {$this->table_name} {$where};")));
            } else {
                // Perform sanitized database query to retrieve results
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT coin_id,description FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} " . esc_sql($args['order']) . " LIMIT %d, %d;",
                        absint($args['offset']),
                        absint($args['number'])
                    )
                );
            }
            // Set results in cache
            wp_cache_set($cache_key, $results, 'coins', 3600);
        }
        return $results;
    }

    /**
     * Retrieves coins meta data from the database
     *
     * @param array $args   Optional. Array of arguments for retrieving data.
     * @param bool  $count  Optional. Whether to count the results. Default false.
     * @return mixed        Results of the database query.
     */
    public function get_coins_meta_data($args = array(), $count = false)
    {
        global $wpdb;

        // Set default values for the arguments
        $defaults = array(
            'number' => 20,
            'offset' => 0,
            'coin_id' => '',
            'status' => '',
            'orderby' => 'id',
            'order' => 'ASC',
        );

        // Merge the provided arguments with the default values
        $args = wp_parse_args($args, $defaults);

        // Ensure the number of results is at least 1
        $args['number'] = absint($args['number']);

        // Initialize the WHERE clause
        $where = '';

        // Sanitize and escape the coin ID input
        if (!empty($args['coin_id'])) {
            if (empty($where)) {
                $where .= " WHERE";
            } else {
                $where .= " AND";
            }
            if (is_array($args['coin_id'])) {
                $coin_ids = array_map('esc_sql', $args['coin_id']);
                $where .= " `coin_id` IN('" . implode("','", $coin_ids) . "') ";
            } else {
                $coin_id = esc_sql($args['coin_id']);
                $where .= " `coin_id` = '" . $coin_id . "' ";
            }
        }

        // Sanitize and escape the orderby input
        $args['orderby'] = !array_key_exists($args['orderby'], $this->get_columns()) ? $this->primary_key : $args['orderby'];
        if ('total' === $args['orderby'] || 'subtotal' === $args['orderby']) {
            $args['orderby'] = esc_sql($args['orderby']) . '+0';
        }

        // Generate cache key
        $cache_key = (true === $count) ? md5('cmc_coins_metadata_count' . serialize($args)) : md5('cmc_coins_metadata_' . serialize($args));

        // Get results from cache or perform database query
        $results = wp_cache_get($cache_key, 'coins');
        if (false === $results) {
            if (true === $count) {
                // Perform sanitized database query to count results
                $results = absint($wpdb->get_var($wpdb->prepare("SELECT COUNT({$this->primary_key}) FROM {$this->table_name} {$where};")));
            } else {
                // Perform sanitized database query to retrieve results
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT coin_id,extra_data FROM {$this->table_name} {$where} ORDER BY {$args['orderby']} " . esc_sql($args['order']) . " LIMIT %d, %d;",
                        absint($args['offset']),
                        absint($args['number'])
                    )
                );
            }
            // Set results in cache
            wp_cache_set($cache_key, $results, 'coins', 3600);
        }
        return $results;
    }

    public function cmcMeta_insert_data($data = null)
    {
        if (isset($data) && !empty($data) && is_array($data)) {
            return $this->wp_insert_rows($data, $this->table_name, true, 'coin_id');
        }
    }
    /**
     * Insert rows into the database table with proper sanitization and escaping
     *
     * @param array  $row_arrays   Array of rows to be inserted
     * @param string $wp_table_name Name of the WordPress table
     * @param bool   $update       Whether to update the row if it already exists
     * @param string $primary_key  Primary key for the table
     * @return bool                True on success, false on failure
     */
    public function wp_insert_rows($row_arrays, $wp_table_name, $update = false, $primary_key = null)
    {
        global $wpdb;
        $wp_table_name = esc_sql($wp_table_name); // Escaping the table name

        // Setup arrays for Actual Values, and Placeholders
        $values = array();
        $place_holders = array();
        $query = "";
        $query_columns = "";
        $floatCols = array('');

        $query .= "INSERT INTO `{$wp_table_name}` ("; // Constructing the INSERT query

        foreach ($row_arrays as $count => $row_array) {
            foreach ($row_array as $key => $value) {
                if ($count == 0) {
                    if ($query_columns) {
                        $query_columns .= ", " . $key . ""; // Building the column names for the INSERT query
                    } else {
                        $query_columns .= "" . $key . "";
                    }
                }

                $values[] = $value; // Storing the values for $wpdb->prepare()

                $symbol = "%s"; // Default symbol for string values
                if (is_numeric($value)) {
                    $symbol = "%d"; // Change symbol to integer if the value is numeric
                }

                if (in_array($key, $floatCols)) {
                    $symbol = "%f"; // Change symbol to float if the column is a float
                }
                if (isset($place_holders[$count])) {
                    $place_holders[$count] .= ", '$symbol'"; // Building the placeholders for $wpdb->prepare()
                } else {
                    $place_holders[$count] = "( '$symbol'"; // Start of new placeholder
                }
            }
            // Closing the placeholder
            $place_holders[$count] .= ")";
        }

        $query .= " $query_columns ) VALUES "; // Completing the INSERT query with column names

        $query .= implode(', ', $place_holders); // Adding the placeholders to the query

        if ($update) {
            $update = " ON DUPLICATE KEY UPDATE $primary_key=VALUES( $primary_key ),"; // Constructing the ON DUPLICATE KEY UPDATE part of the query
            $cnt = 0;
            foreach ($row_arrays[0] as $key => $value) {
                if ($cnt == 0) {
                    $update .= "$key=VALUES($key)"; // Adding the column names for update
                    $cnt = 1;
                } else {
                    $update .= ", $key=VALUES($key)"; // Adding the column names for update
                }
            }
            $query .= $update; // Adding the update part to the query
        }

        $sql = $wpdb->prepare($query, $values); // Sanitizing and preparing the query

        if ($wpdb->query($sql)) {
            return true; // Return true on successful query execution
        } else {
            return false; // Return false on query failure
        }
    }

    /**
     * Return the number of results found for a given query
     *
     * @param  array  $args Array of arguments for querying coins
     * @return int         Number of results found
     */
    public function count($args = array())
    {
        return $this->get_coins($args, true); // Delegate the counting to the get_coins method
    }
    /**
     * Create the table
     *
     * @access  public
     * @since   1.0
     */
    public function create_table()
    {
        global $wpdb;

        // Include the necessary file for database upgrades
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Define the SQL query for creating the table with proper sanitization and escaping
        $sql = "CREATE TABLE IF NOT EXISTS " . esc_sql($this->table_name) . " (
		id bigint(20) NOT NULL AUTO_INCREMENT,
		coin_id varchar(200) NOT NULL,
		extra_data longtext NOT NULL,
		description longtext NOT NULL,
		last_updated TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(),
		PRIMARY KEY (id),
		UNIQUE (coin_id)
		) CHARACTER SET utf8 COLLATE utf8_general_ci;";

        // Execute the SQL query using $wpdb->query() after sanitization and escaping
        $wpdb->query($sql);

        // Update the database version option after sanitization and escaping
        update_option(esc_sql($this->table_name) . '_db_version', esc_sql($this->version));
    }

    /**
     * Drop the table if it exists
     *
     * @access  public
     * @since   1.0
     */
    public function drop_table()
    {
        global $wpdb;

        // Drop the table if it exists after proper sanitization and escaping
        $wpdb->query('DROP TABLE IF EXISTS ' . esc_sql($this->table_name));
    }
}
